﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.IO;
using System.Xml.Linq;
using System.Data.SQLite;

namespace StatisticsAggregator
{
    class ReportsHelper
    {
        public static long CreateUser(String s, String f, String m, int experience, string role)
        {
            var userTable = new StatisticsDataSetTableAdapters.UserTableAdapter();

            long? userId = userTable.GetUserId(s, f, m);

            // new user
            if (!userId.HasValue)
            {
                using (var command = userTable.Connection.CreateCommand())
                {
                    userTable.Connection.Open();

                    command.CommandText = @"
                        INSERT INTO [User] ([SecondName], [FirstName], [MiddleName], [Experience], [Role]) VALUES (@s, @f, @m, @ex, @ro);
                        SELECT last_insert_rowid()
                        ";

                    command.Parameters.Add(new SQLiteParameter("@s", s));
                    command.Parameters.Add(new SQLiteParameter("@f", f));
                    command.Parameters.Add(new SQLiteParameter("@m", m));
                    command.Parameters.Add(new SQLiteParameter("@ex", experience));
                    command.Parameters.Add(new SQLiteParameter("@ro", role));

                    userId = Convert.ToInt64(command.ExecuteScalar());

                    //userTable.Connection.Close();
                }
            }

            return userId.Value;
        }

        public static long CreateUserInfoEntry(long userId, string key, string value)
        {
            long entryId = 0;
            var userInfoTable = new StatisticsDataSetTableAdapters.UserInfoTableAdapter();
            userInfoTable.Connection.Open();

            using (var command = userInfoTable.Connection.CreateCommand())
            {
                command.CommandText = @"
                        INSERT INTO [UserInfo] ([UserId], [Key], [Value]) VALUES (@id, @k, @v);
                        SELECT last_insert_rowid()
                        ";

                command.Parameters.Add(new SQLiteParameter("@id", userId));
                command.Parameters.Add(new SQLiteParameter("@k", key));
                command.Parameters.Add(new SQLiteParameter("@v", value));

                entryId = Convert.ToInt64(command.ExecuteScalar());
            }

            return entryId;
        }

        public static void RegisterStudent(long userId, String faculty, String specialty, int? course)
        {
            if (!String.IsNullOrEmpty(faculty))
                CreateUserInfoEntry(userId, "Faculty", faculty);

            if (!String.IsNullOrEmpty(specialty))
                CreateUserInfoEntry(userId, "Specialty", specialty);

            if (course.HasValue)
                CreateUserInfoEntry(userId, "Course", course.Value.ToString());
        }

        public static void RegisterWorker(long userId, String job, int? seniority)
        {
            if (!String.IsNullOrEmpty(job))
                CreateUserInfoEntry(userId, "Job", job);

            if (seniority.HasValue)
                CreateUserInfoEntry(userId, "Seniority", seniority.Value.ToString());
        }

        public static long CreateReport(String uid, DateTime date, long userId)
        {
            var reportTable = new StatisticsDataSetTableAdapters.ReportTableAdapter();
            long reportId = -1;

            using (var command = reportTable.Connection.CreateCommand())
            {
                reportTable.Connection.Open();

                command.CommandText = @"
                    INSERT INTO [Report] ([Uid], [Date], [UserId]) VALUES (@uid, @date, @userId);
                    SELECT last_insert_rowid()
                    ";

                command.Parameters.Add(new SQLiteParameter("@uid", uid));
                command.Parameters.Add(new SQLiteParameter("@date", date));
                command.Parameters.Add(new SQLiteParameter("@userId", userId));

                reportId = Convert.ToInt64(command.ExecuteScalar());

                //reportTable.Connection.Close();
            }

            return reportId;
        }

        public static long CreateMisprint(long reportId, string type)
        {
            var misprintTable = new StatisticsDataSetTableAdapters.MisprintTableAdapter();
            long misprintId = -1;

            using (var command = misprintTable.Connection.CreateCommand())
            {
                misprintTable.Connection.Open();

                command.CommandText = @"
                    INSERT INTO [Misprint] ([ReportId], [Type]) VALUES (@reportId, @type);
                    SELECT last_insert_rowid()
                    ";

                command.Parameters.Add(new SQLiteParameter("@reportId", reportId));
                command.Parameters.Add(new SQLiteParameter("@type", type));

                misprintId = Convert.ToInt64(command.ExecuteScalar());

                //misprintTable.Connection.Close();
            }

            return misprintId;
        }

        public static void LoadFromXml(Stream stream, out int loadedReports, out int totalReports)
        {
            loadedReports = 0;
            totalReports = 0;

            var reportTable = new StatisticsDataSetTableAdapters.ReportTableAdapter();
            var misprintLetterTable = new StatisticsDataSetTableAdapters.MisprintLetterTableAdapter();

            var xdoc = XDocument.Load(new StreamReader(stream));

            var reports = from report in xdoc.Element("ReportEnvelope").Elements("MisprintTestingToolReport")
                select new
                {
                    Date = report.Attribute("date").Value,
                    Uid = report.Attribute("uid").Value,
                    User = report.Element("UserSection"),
                    Misprints = report.Element("MisprintsSection").Elements("Misprint")
                };

            if (reports.Count() == 0)
                return;

            foreach (var report in reports)
            {
                totalReports++;

                bool isAlreadyLoaded = Convert.ToInt32(reportTable.CountReportsByUid(report.Uid).ToString()) != 0;
                if (isAlreadyLoaded)
                    continue;

                long userId = CreateUser(report.User.Element("SecondName").Value,
                    report.User.Element("FirstName").Value,
                    report.User.Element("MiddleName").Value,
                    int.Parse(report.User.Element("ExperienceSkillsPC").Value),
                    report.User.Attribute("role").Value);

                switch (report.User.Attribute("role").Value)
                {
                    case "student":
                        int? course = null;
                        int tCourse;

                        if (int.TryParse(report.User.Element("Course").Value, out tCourse))
                            course = tCourse;

                        RegisterStudent(userId,
                            report.User.Element("Faculty").Value,
                            report.User.Element("Specialty").Value,
                            course);
                        break;

                    case "worker":
                        int? seniority = null;
                        int tSeniority;

                        if (int.TryParse(report.User.Element("SeniorityWork").Value, out tSeniority))
                            seniority = tSeniority;

                        RegisterWorker(userId,
                            report.User.Element("Job").Value,
                            seniority);
                        break;
                }

                // add report
                long reportId = CreateReport(report.Uid, DateTime.Parse(report.Date), userId);

                foreach (var misprint in report.Misprints)
                {
                    long misprintId = CreateMisprint(reportId, misprint.Attribute("type").Value);
                    
                    string distance = misprint.Attribute("distance") != null ? misprint.Attribute("distance").Value : "";

                    int order = 1;
                    foreach (var misprintLetter in misprint.Elements("Letter"))
                    {
                        misprintLetterTable.InsertQuery(misprintLetter.Value.ToUpper(), distance, order, misprintId);
                        order++;
                    }
                }

                loadedReports++;
            }
        }

        public static int DeleteReport(long reportId)
        {
            var reportTable = new StatisticsDataSetTableAdapters.ReportTableAdapter();
            var misprintTable = new StatisticsDataSetTableAdapters.MisprintTableAdapter();
            var misprintLetterTable = new StatisticsDataSetTableAdapters.MisprintLetterTableAdapter();
            var reportMisprintsViewTable = new StatisticsDataSetTableAdapters.ReportMisprintsViewTableAdapter();

            long? userId = reportTable.GetUserIdByReportId(reportId);

            foreach (var row in reportMisprintsViewTable.GetData(reportId))
            {
                misprintTable.DeleteQuery(row.MisprintId);
                misprintLetterTable.DeleteQuery(row.MisprintId);
            }

            int affectedRows = reportTable.DeleteQuery(reportId);

            if (Convert.ToInt32(reportTable.CountReportsByUserId(userId.Value)) == 0)
            {
                var userTable = new StatisticsDataSetTableAdapters.UserTableAdapter();
                userTable.DeleteQuery(userId.Value);
            }

            return affectedRows;
        }
    }
}
